﻿using System;
using System.Data;
using System.Data.SqlClient;
using prjKQXS_DLL;

namespace prjKQXS_DLL.Ve_so
{
    public class DMVETT_DAL
    {
        public int InsertDMVE(DMVETT dmve)
        {
            SqlConnection conn = new SqlConnection(BienTc.connString);
            conn.Open();
            SqlCommand sqlCommand1 = new SqlCommand("Delete from KETQUATT Where Convert(Datetime,Ngaymothuong,103) = Convert(Datetime,@Ngay,103)", conn);
            sqlCommand1.Parameters.AddWithValue("@Ngay", dmve.Ngaymothuong);
            sqlCommand1.ExecuteNonQuery();
            SqlCommand sqlCommand = new SqlCommand("insert into [KETQUATT](Giave,Kyhieu,Ngaymothuong,Pchutich,Congty,Sotuphap,Sotaichinh,Congantinh,MattranTQ,ThanhtraNN,Thuky) values(@Giave,@Kyhieu,@Ngay,@Pchutich,@Congty,@Sotuphap,@Sotaichinh,@Congantinh,@MattranTQ,@ThanhtraNN,@Thuky)", conn);
            try
            {
                sqlCommand.Parameters.AddWithValue("@Giave", dmve.Giave);
                sqlCommand.Parameters.AddWithValue("@Kyhieu", dmve.Kyhieu);
                sqlCommand.Parameters.AddWithValue("@Ngay", dmve.Ngaymothuong);
                sqlCommand.Parameters.AddWithValue("@Pchutich", dmve.Pchutich);
                sqlCommand.Parameters.AddWithValue("@Congty", dmve.Congty);
                sqlCommand.Parameters.AddWithValue("@Sotuphap", dmve.Sotuphap);
                sqlCommand.Parameters.AddWithValue("@Sotaichinh", dmve.Sotaichinh);
                sqlCommand.Parameters.AddWithValue("@Congantinh", dmve.Congantinh);
                sqlCommand.Parameters.AddWithValue("@MattranTQ", dmve.MattranTQ);
                sqlCommand.Parameters.AddWithValue("@ThanhtraNN", dmve.ThanhtranNN);
                sqlCommand.Parameters.AddWithValue("@Thuky", dmve.Thuky);
                return sqlCommand.ExecuteNonQuery();
            }
            catch
            {
                throw;
            }
            finally
            {
                sqlCommand.Dispose();
                sqlCommand1.Dispose();
                conn.Close();
                conn.Dispose();
            }
        }

        public bool Kiemtrakyhieuboc(String ma)
        {
            bool retVal = false;
            SqlConnection conn = new SqlConnection(BienTc.connString);
            try
            {
                SqlCommand cmd = new SqlCommand("Select count(*) from Ketquaveboc5 where Kyhieu = @Ma", conn);
                cmd.Parameters.AddWithValue("@Ma", ma);
                conn.Open();
                if (cmd.ExecuteScalar().ToString() == "0")
                {
                    retVal = true;
                }
                else
                {
                    retVal = false;
                }
            }
            catch (Exception)
            {
                retVal = false;
            }
            finally
            {
            }
            return retVal;
        }
        public DataTable Loadveboc()
        {
            SqlConnection conn = new SqlConnection(BienTc.connString);
            DataTable dt = new DataTable();
            try
            {
                SqlDataAdapter da = new SqlDataAdapter(@"Select ID,Kyhieu,Giave,Ngayquayso from Ketquaveboc5", conn);
                da.Fill(dt);
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
            return dt;
        }
        public int InsertDMVEBOC(DMVETT dmve)
        {
            SqlConnection conn = new SqlConnection(BienTc.connString);
            conn.Open();
            SqlCommand sqlCommand1 = new SqlCommand("Delete from Ketquaveboc5 Where Kyhieu = @kyhieu", conn);
            sqlCommand1.Parameters.AddWithValue("@kyhieu", dmve.Kyhieu);
            sqlCommand1.ExecuteNonQuery();
            SqlCommand sqlCommand = new SqlCommand("insert into [Ketquaveboc5](Ngayquayso,Kyhieu,Giave,gnhat1,gnhat2,gnhat3,gnhat4,gnhi1,gba1,gtu1,gtu2,gtu3,gtu4,gtu5,gtu6,gtu7,gtu8,gtu9,gtu10,gtu11,gtu12,gtu13,gtu14,gtu15,gtu16,gnam1) values(@Ngayquayso,@Kyhieu,@Giave,@gnhat1,@gnhat2,@gnhat3,@gnhat4,@gnhi1,@gba1,@gtu1,@gtu2,@gtu3,@gtu4,@gtu5,@gtu6,@gtu7,@gtu8,@gtu9,@gtu10,@gtu11,@gtu12,@gtu13,@gtu14,@gtu15,@gtu16,@gnam1)", conn);
            try
            {
                sqlCommand.Parameters.AddWithValue("@Giave", dmve.Giave);
                sqlCommand.Parameters.AddWithValue("@Kyhieu", dmve.Kyhieu);
                sqlCommand.Parameters.AddWithValue("@Ngayquayso", dmve.Ngaymothuong);
                sqlCommand.Parameters.AddWithValue("@gnhat1", dmve.gnhat1);
                sqlCommand.Parameters.AddWithValue("@gnhat2", dmve.gnhat2);
                sqlCommand.Parameters.AddWithValue("@gnhat3", dmve.gnhat3);
                sqlCommand.Parameters.AddWithValue("@gnhat4", dmve.gnhat4);
                sqlCommand.Parameters.AddWithValue("@gnhi1", dmve.gnhi1);
                sqlCommand.Parameters.AddWithValue("@gba1", dmve.gba1);
                sqlCommand.Parameters.AddWithValue("@gtu1", dmve.gtu1);
                sqlCommand.Parameters.AddWithValue("@gtu2", dmve.gtu2);
                sqlCommand.Parameters.AddWithValue("@gtu3", dmve.gtu3);
                sqlCommand.Parameters.AddWithValue("@gtu4", dmve.gtu4);
                sqlCommand.Parameters.AddWithValue("@gtu5", dmve.gtu5);
                sqlCommand.Parameters.AddWithValue("@gtu6", dmve.gtu6);
                sqlCommand.Parameters.AddWithValue("@gtu7", dmve.gtu7);
                sqlCommand.Parameters.AddWithValue("@gtu8", dmve.gtu8);
                sqlCommand.Parameters.AddWithValue("@gtu9", dmve.gtu9);
                sqlCommand.Parameters.AddWithValue("@gtu10", dmve.gtu10);
                sqlCommand.Parameters.AddWithValue("@gtu11", dmve.gtu11);
                sqlCommand.Parameters.AddWithValue("@gtu12", dmve.gtu12);
                sqlCommand.Parameters.AddWithValue("@gtu13", dmve.gtu13);
                sqlCommand.Parameters.AddWithValue("@gtu14", dmve.gtu14);
                sqlCommand.Parameters.AddWithValue("@gtu15", dmve.gtu15);
                sqlCommand.Parameters.AddWithValue("@gtu16", dmve.gtu16);
                sqlCommand.Parameters.AddWithValue("@gnam1", dmve.gnam1);
                return sqlCommand.ExecuteNonQuery();
            }
            catch
            {
                throw;
            }
            finally
            {
                sqlCommand.Dispose();
                sqlCommand1.Dispose();
                conn.Close();
                conn.Dispose();
            }
        }
         public string SelectDMVEBOC(string tenCot, string Kyhieu)
        {
            string kq = "";
            SqlConnection conn = new SqlConnection(BienTc.connString);
            conn.Open();
            SqlCommand sqlCommand1 = new SqlCommand("select " + tenCot.Trim() + " from Ketquaveboc5 Where Kyhieu = @kyhieu", conn);
            sqlCommand1.Parameters.AddWithValue("@kyhieu", Kyhieu);
            SqlDataReader dr = sqlCommand1.ExecuteReader();
            try
            {
                if (dr.Read())
                {
                    kq = (string)dr[tenCot];
                }
                return kq;
            }
            catch
            {
                throw;
            }
            finally
            {

                sqlCommand1.Dispose();
                conn.Close();
                conn.Dispose();
            }
        }
        //================================
        public bool Kiemtrakyhieuboc2(String ma)
        {

            bool retVal = false;
            SqlConnection conn = new SqlConnection(BienTc.connString);
            try
            {
                SqlCommand cmd = new SqlCommand("Select count(*) from Ketquaveboc2 where Kyhieu = @Ma", conn);
                cmd.Parameters.AddWithValue("@Ma", ma);
                conn.Open();
                if (cmd.ExecuteScalar().ToString() == "0")
                {
                    retVal = true;
                }
                else
                {
                    retVal = false;
                }
            }
            catch (Exception)
            {
                retVal = false;
            }
            finally
            {
            }
            return retVal;
        }
        public DataTable Loadveboc2()
        {
            SqlConnection conn = new SqlConnection(BienTc.connString);
            DataTable dt = new DataTable();
            try
            {
                SqlDataAdapter da = new SqlDataAdapter(@"Select ID,Kyhieu,Giave,Ngayquayso from Ketquaveboc2", conn);
                da.Fill(dt);
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
            return dt;
        }
        public int InsertDMVEBOC1(DMVETT dmve)
        {
            SqlConnection conn = new SqlConnection(BienTc.connString);
            conn.Open();
            SqlCommand sqlCommand1 = new SqlCommand("Delete from Ketquaveboc2 Where Kyhieu = @kyhieu", conn);
            sqlCommand1.Parameters.AddWithValue("@kyhieu", dmve.Kyhieu);
            sqlCommand1.ExecuteNonQuery();
            SqlCommand sqlCommand = new SqlCommand("insert into [Ketquaveboc2](Ngayquayso,Kyhieu,Giave,gnhat1,gnhat2,gnhat3,gnhat4,gnhi1,gba1,gtu1,gtu2,gnam1,gnam2,gnam3,gnam4,gnam5,gnam6,gnam7,gnam8,gnam9,gnam10,gnam11,gnam12,gnam13,gnam14,gnam15,gnam16,gnam17,gnam18) values(@Ngayquayso,@Kyhieu,@Giave,@gnhat1,@gnhat2,@gnhat3,@gnhat4,@gnhi1,@gba1,@gtu1,@gtu2,@gnam1,@gnam2,@gnam3,@gnam4,@gnam5,@gnam6,@gnam7,@gnam8,@gnam9,@gnam10,@gnam11,@gnam12,@gnam13,@gnam14,@gnam15,@gnam16,@gnam17,@gnam18)", conn);
            try
            {
                sqlCommand.Parameters.AddWithValue("@Giave", dmve.Giave);
                sqlCommand.Parameters.AddWithValue("@Kyhieu", dmve.Kyhieu);
                sqlCommand.Parameters.AddWithValue("@Ngayquayso", dmve.Ngaymothuong);
                sqlCommand.Parameters.AddWithValue("@gnhat1", dmve.gnhat1);
                sqlCommand.Parameters.AddWithValue("@gnhat2", dmve.gnhat2);
                sqlCommand.Parameters.AddWithValue("@gnhat3", dmve.gnhat3);
                sqlCommand.Parameters.AddWithValue("@gnhat4", dmve.gnhat4);
                sqlCommand.Parameters.AddWithValue("@gnhi1", dmve.gnhi1);
                sqlCommand.Parameters.AddWithValue("@gba1", dmve.gba1);
                sqlCommand.Parameters.AddWithValue("@gtu1", dmve.gtu1);
                sqlCommand.Parameters.AddWithValue("@gtu2", dmve.gtu2);
                sqlCommand.Parameters.AddWithValue("@gnam1", dmve.gnam1);
                sqlCommand.Parameters.AddWithValue("@gnam2", dmve.gnam2);
                sqlCommand.Parameters.AddWithValue("@gnam3", dmve.gnam3);
                sqlCommand.Parameters.AddWithValue("@gnam4", dmve.gnam4);
                sqlCommand.Parameters.AddWithValue("@gnam5", dmve.gnam5);
                sqlCommand.Parameters.AddWithValue("@gnam6", dmve.gnam6);
                sqlCommand.Parameters.AddWithValue("@gnam7", dmve.gnam7);
                sqlCommand.Parameters.AddWithValue("@gnam8", dmve.gnam8);
                sqlCommand.Parameters.AddWithValue("@gnam9", dmve.gnam9);
                sqlCommand.Parameters.AddWithValue("@gnam10", dmve.gnam10);
                sqlCommand.Parameters.AddWithValue("@gnam11", dmve.gnam11);
                sqlCommand.Parameters.AddWithValue("@gnam12", dmve.gnam12);
                sqlCommand.Parameters.AddWithValue("@gnam13", dmve.gnam13);
                sqlCommand.Parameters.AddWithValue("@gnam14", dmve.gnam14);
                sqlCommand.Parameters.AddWithValue("@gnam15", dmve.gnam15);
                sqlCommand.Parameters.AddWithValue("@gnam16", dmve.gnam16);
                sqlCommand.Parameters.AddWithValue("@gnam17", dmve.gnam17);
                sqlCommand.Parameters.AddWithValue("@gnam18", dmve.gnam18);
                return sqlCommand.ExecuteNonQuery();
            }
            catch
            {
                throw;
            }
            finally
            {
                sqlCommand.Dispose();
                sqlCommand1.Dispose();
                conn.Close();
                conn.Dispose();
            }
        }
        public string SelectDMVEBOC2(string tenCot, string Kyhieu)
        {
            string kq = "";
            SqlConnection conn = new SqlConnection(BienTc.connString);
            conn.Open();
            SqlCommand sqlCommand1 = new SqlCommand("select " + tenCot.Trim() + " from Ketquaveboc2 Where Kyhieu = @kyhieu", conn);
            sqlCommand1.Parameters.AddWithValue("@kyhieu", Kyhieu);
            SqlDataReader dr = sqlCommand1.ExecuteReader();
            try
            {
                if (dr.Read())
                {
                    kq = (string)dr[tenCot];
                }
                return kq;
            }
            catch
            {
                throw;
            }
            finally
            {

                sqlCommand1.Dispose();
                conn.Close();
                conn.Dispose();
            }
        }
    
    }
}

